package com.yuanlrc.base.dao.admin;

import com.yuanlrc.base.entity.admin.Borrow;
import com.yuanlrc.base.entity.admin.StudentNameCount;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BorrowDao extends JpaRepository<Borrow,Long>, JpaSpecificationExecutor<Borrow> {

    @Query("select bor from Borrow bor where bor.id= :id")
   public Borrow find(@Param("id") Long id);

    /**
     * 查询借阅图书排行
     * @param
     * @return list
     */
    @Query(nativeQuery=true,value=" Select b.book_name,COUNT(b.id) as book_count \n" +
            "from ylrc_book b JOIN ylrc_borrow br on b.id = br.book_id \n" +
            "GROUP BY b.id ORDER BY COUNT(b.id) desc\n " +
            "LIMIT 10\n")
    public List<Object> findBookNameCount();
/**
 * 查询用户借书排行
 * @param
 * @return list
 */

    @Query(value=" SELECT s.student_name ,COUNT(s.id)as student_count from ylrc_student s join ylrc_borrow br on s.id=br.student_id\n" +
            " GROUP BY s.id\n" +
            " ORDER BY COUNT(s.id) desc  \n" +
            " LIMIT 10",nativeQuery=true)
    public List<Object> findStudentNameCount();
    /**
     * 查询用户借书数量排行
     * @param
     * @return list
     */
    @Query(value="select s.student_name,sum(b.borrow_num) from ylrc_borrow b join ylrc_student s on s.id=b.student_id\n" +
            "GROUP BY s.student_name order  by sum(b.borrow_num) DESC Limit 10",nativeQuery=true)
    public List<Object> findStudentBorrowNum();


//    @Query(value = "select * from ylrc_borrow where student_id=:studentId",nativeQuery = true)
    public List<Borrow> findByStudentId(@Param("studentId") Long studentId);
}
